using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.IO;


namespace Update
{
    public class SqlConnecitonClass
    {
        
        //SqlConnection conn = new SqlConnection(@"Server=MRT-BILGISAYAR;Database=db_060702011;Uid=sa;Pwd=112233");

        public SqlCommand comm = new SqlCommand();
        public SqlCommand ncomm = new SqlCommand();
        public string nsqlQuery = "exec sp_reset_connection";
            //"set quoted_identifier on set arithabort off set numeric_roundabort on set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7  set transaction isolation level read committed ";
        SqlDataAdapter sda = new SqlDataAdapter();
        public string AppPath = System.Windows.Forms.Application.StartupPath; 
        
     //StreamReader sr = File.OpenText("D:\\TkPrg\\ayar.INI");
     //       string okunan = sr.ReadLine();

     //       //SqlConnection conn = new SqlConnection(@"Server=hp1\sqlexp2008;Database=TkData;integrated security=true");
     //       SqlConnection conn = new SqlConnection(@""+okunan+"");

        public int ExecuteQuery(string sqlQuery, CommandType commandType)
        {


            StreamReader sr = File.OpenText("" + AppPath + "\\AYAR.INI");
            string okunan = sr.ReadLine();

            //SqlConnection conn = new SqlConnection(@"Server=hp1\sqlexp2008;Database=TkData;integrated security=true");
            SqlConnection conn = new SqlConnection(@"" + okunan + "");
            
            
            try
            {
                comm.Connection = conn;
                comm.CommandText = sqlQuery;
                comm.CommandType = commandType;
                //comm.CommandTimeout = 900000000;
              //  ncomm.CommandText = nsqlQuery;
                

                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                int ef=comm.ExecuteNonQuery();
                int aa = ncomm.ExecuteNonQuery();
                conn.Close();
                return ef;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Data  "+sqlQuery.ToString()+"", ex.Message.ToString() + "-" + sqlQuery.ToString());

                conn.Close();
                return 0;
            }
        }

        public void AddParameter(string parameterName, object _value)
        {
            comm.Parameters.AddWithValue(parameterName, _value);
        }

        public void ClearParameter()
        {
            comm.Parameters.Clear();
        }

        public DataTable ShowTable(string sqlQuery, CommandType commandType)
        {
            StreamReader sr = File.OpenText("" + AppPath + "\\AYAR.INI");
            string okunan = sr.ReadLine();

            //SqlConnection conn = new SqlConnection(@"Server=hp1\sqlexp2008;Database=TkData;integrated security=true");
            SqlConnection conn = new SqlConnection(@"" + okunan + "");




            try
            {
                DataTable dt = new DataTable();
                comm.Connection = conn;
                comm.CommandText = sqlQuery;
                comm.CommandType = commandType;
              //  comm.CommandTimeout = 900000000;
               ncomm.CommandText = nsqlQuery;
                sda.SelectCommand = comm;

                sda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {

                MessageBox.Show("Data", ex.Message.ToString() + "-" + sqlQuery.ToString());
                conn.Close();
                return null;
            }
        }

        public Object CommScalar(string sqlQuery, CommandType commandType)
        {
            StreamReader sr = File.OpenText("" + AppPath + "\\AYAR.INI");
            string okunan = sr.ReadLine();

            //SqlConnection conn = new SqlConnection(@"Server=hp1\sqlexp2008;Database=TkData;integrated security=true");
            SqlConnection conn = new SqlConnection(@"" + okunan + "");



            try
            {
                object obj;
                comm.Connection = conn;
                comm.CommandText = sqlQuery;
                comm.CommandType = commandType;
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                
                obj = comm.ExecuteScalar();

                return obj;
            }
            catch (Exception ex)
            {

                MessageBox.Show("Data"+""+sqlQuery.ToString()+"", ex.Message.ToString() + "-" + sqlQuery.ToString());
                conn.Close();
                return null;
            }

        }

        public DataSet ShowDataSet(string sqlQuery, CommandType commandType)
        {
            StreamReader sr = File.OpenText("" + AppPath + "\\AYAR.INI");
            string okunan = sr.ReadLine();

            //SqlConnection conn = new SqlConnection(@"Server=hp1\sqlexp2008;Database=TkData;integrated security=true");
            SqlConnection conn = new SqlConnection(@"" + okunan + "");


            try
            {
                DataSet dt = new DataSet();
                comm.Connection = conn;
                comm.CommandText = sqlQuery;
                comm.CommandType = commandType;
                //comm.CommandTimeout = 900000000;
                sda.SelectCommand = comm;

                sda.Fill(dt);

                return dt;
            }
            catch (Exception)
            {
                conn.Close();
                return null;
            }
        }




    }
}